# -*- coding: utf-8 -*-
# @Time    : 2022/7/19 14:15
# @Author  : Yanfeng
# @Email   : sxyanfeng2014@163.com
# @File    : db_utils.py
# @Software: PyCharm



from typing import Optional
from datetime import datetime
from typing import List, Union, Text, Dict, Tuple
import pymysql
from utils.readFileUtils.yaml_control import GetYamlData





class DB:
    # mysql = GetYamlData().get_yaml_data()["znkd_database"]

    def __init__(self, database="znkd_database"):
        """
        初始化数据库连接，并指定查询的结果集以字典形式返回
        """
        self.mysql = GetYamlData().get_yaml_data()[database]
        self.connection = pymysql.connect(
            host=self.mysql['host'],
            port=self.mysql['port'],
            user=self.mysql['user'],
            password=self.mysql['password'],
            db=self.mysql['db_name']
        )

    def execute_sql(self, sql: Text, value: Tuple) -> Union[dict, None]:
        """
        执行sql语句方法，查询所有结果的sql只会返回一条结果（
        比如说： 使用select * from cases , 结果将只会返回第一条数据    {'id': 1, 'name': 'updatehahaha', 'path': None, 'body': None, 'expected': '{"msg": "你好"}', 'api_id': 1, 'create_at': '2021-05-17 17:23:54', 'update_at': '2021-05-17 17:23:54'}

        ），支持select， delete， insert， update
        :param sql: sql语句
        :return: select 语句 如果有结果则会返回 对应结果字典，delete，insert，update 将返回None
        """
        with self.connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, value)
            result = cursor.fetchone()
            # 使用commit解决查询数据出现概率查错问题
            self.connection.commit()
            # return self.verify(rself.close()
            return result


    def execute_sql_many(self, sql: Text):

        """

        :param sql:
        :return:
        """
        with self.connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql)
            result = cursor.fetchall()
            self.connection.commit()
            return result

    def execute_sql_many1(self, sql: Text, value: Tuple):
        """

        :param sql:
        :return:
        """
        with self.connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, value)
            result = cursor.fetchall()
            self.connection.commit()
            return result

    def verify(self, result: dict) -> Union[dict, None]:
        """验证结果能否被json.dumps序列化"""
        # 尝试变成字符串，解决datetime 无法被json 序列化问题
        try:
            json.dumps(result)
        except TypeError:  # TypeError: Object of type datetime is not JSON serializable
            for k, v in result.items():
                if isinstance(v, datetime):
                    result[k] = str(v)
        return result



    def execute(self, sql: str, value: Optional[Tuple] = None) -> int:
        if value is not None and not isinstance(value, tuple):
            raise ValueError("The value parameter must be a tuple or None.")

        with self.connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
            if value is None:
                try:
                    rows = cursor.execute(sql)
                except Exception as e:
                    self.connection.rollback()
                    raise e

            else:
                try:
                    rows = cursor.execute(sql, value)
                except Exception as e:
                    self.connection.rollback()
                    raise e
            self.connection.commit()
            return rows

    def execute1(self, sql: Text, value: Tuple):
        """
            更新 、 删除、 新增
            :param sql:
            :return:
            """

        cursor = self.connection.cursor(cursor=pymysql.cursors.DictCursor)
        rows1 = cursor.execute(sql, value)

        return rows1

    def close(self):
        """关闭数据库连接"""
        self.connection.close()


if __name__ == '__main__':
    db = DB()
    sql2 = """SELECT SellerSku FROM m_ebay_listing_sub WHERE CustomerId=1 AND OrderSourceId=%s AND ProductId=%s;"""
    sql3 = """SELECT SUM(Sales) FROM m_ebay_sale_day_summary WHERE CustomerId=1 AND MarketId=2 AND SellerSku in %s AND Date >= DATE_ADD(CURDATE(),INTERVAL -90 DAY);"""
    sjsehe_ids = ["0056129Y1GB9UU","0056142CDDMPVV","005614AOJ7DEJ1","005614JXYP5MZK"]
    SellerSku = db.execute_sql_many1(sql=sql3, value=(sjsehe_ids,))
    print(SellerSku)
